SQL

对数据库进行管理的语言

分类:

MYSQL安装

打开installer -> 选择developr default -> 显示excute就点excute,没有就next & no -> 等待安装 -> 设置密码

MYSQL备份

mysqldump -h服务器名 -u用户名 -p密码 --routines --default-character-set=utf8 数据库名 > 文件位置/文件名.sql

数据类型

Text 类型

数据类型 描述
CHAR(size) 保存固定长度的字符串(可包含字母、数字以及特殊字符)(最大255)
VARCHAR(size) 保存可变长度的字符串(可包含字母、数字以及特殊字符)(最大255)
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
- ENUM(x,y,z,etc.)
SET 与 ENUM 类似,SET 最多只能包含 64 个列表项,不过 SET 可存储一个以上的值。

Number 类型

数据类型 描述
TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规
FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型,允许固定的小数点。

Time 类型

日期时间类型 日期格式 最小值
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00
TIMESTAMP YYYY-MM-DD HH:MM:SS 19700101080001
DATE YYYY-MM-DD 1000-01-01
TIME HH:MM:SS -838:59:59
YEAR YYYY 1901

数据库表字段类型规范

内置函数

生日

ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)

表内数据增删改查

增删改查

最基础、最实用的功能

查询 SELECT

SELECT 列名 FROM 表明 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 (ASC/DESC) LIMIT 起始行,总条数

普通查询

SELECT 列名 FROM 表名

表名、列名命名

表名/列名 (AS) 名称

查询结果去重

DISTINCT 列名

排序查询

依据单列

SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DSC

依据多列

SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DESC , 列名 ASC/DCS

条件查询

等值/不等值查询

=,>,<,>=,<=,!=/<>

逻辑判断

AND,OR,NOT

区间判断

BETWEEN AND
闭区间,两个数都包含,必须小值在前,大值在后

NULL值判断

列名 IS/IS NOT NULL

枚举查询

列名 IN(1,值2,值3)

模糊查询

列名 LIKE 'A_'
列名 LIKE 'A%'

长度不大于6个的字符串

NOT (列名 LIKE '______%')

分支结构查询

CASE
	WHEN 条件1 THEN 结果1
	……
	ELSE 结果
END

时间查询

SELECT 时间函数(参数列表)
时间函数 描述
SYSDATE() 获取系统时间
CURDATE() 获取当前日期
CURTIME() 获取当前时间
WEEK(DATE) 获取指定日期为一年中的第几周
YEAR(DATE) 获取指定日期的年份
HOUR(DATE) 获取指定时间的小时值
MINUTE(TIME) 获取指定时间的分钟值
DATEDIFF(DATE1,DATE2) 计算DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N) 计算DATE加上N天后的日期

字符串查询

SELECT 字符串函数([参数列表])
字符串函数 说明
CONCAT(str1,str2……) 将多个字符串连接
INSERT(str,pos,len,newStr) 将str中指定pos位置开始len长度的内容替换为newStr
LOWER(str) 将指定字符串转换为小写
UPPER(str) 将指定字符串转换为大写
SUBSTR(str,num,len) 将str字符串指定num位置开始截取len个内容,len为NULL时截取到末尾

聚合函数

SELECT 聚合函数(列名) FROM 表名
聚合函数 说明
SUM() 求单列结果的总和
AVG() 求单列结果的平均值
MAX() 求单列结果的最大值
MIN() 求单列结果的最小值
COUNT() 求总行数
ALL() 全部
ANY() 任一

分组查询

SELECT 列名 FROM 表名 GROUP BY 列名

分组过滤查询

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

限定查询(分页的应用)

SELECT 列名 FROM 表名 LIMIT 起始行, 查询行数

子查询

SELECT 列名 FROM 表名 WHERE EXISTS(子查询)
SELECT 列名 FROM 表名(子查询) WHERE 条件

合并查询

SELECT * FROM 表名1 UNION(去重)/UNION ALL(不去重) SELECT * FROM 表名2

表连接查询

SELECT 列名 FROM1 连接方式 表2 ON 连接条件

表链接查询

删除 DELETE

DELETE FROM 表名 WHERE 条件

修改 UPDATE

UPDATE 表名 SET1=1,2=2 WHERE 条件

增加 INSERT

INSERT INTO 表名(1,2..) VALUES(1,2)

清空 TRUNCATE

清空表的数据

TRUNCATE TABLE 表名

表的操作

创建表

基本语句
CREATE TABLE 表名(
	列名 数据类型[约束],
	列名 数据类型[约束],
	列名 数据类型[约束],
	……
)CHARSET=utf8

优化

主键约束 PRIMARY KEY

不能为 NULL

CREATE TABLE 表名(
	列名 数据类型,
	PRIMARY KEY(列名)
	……
)CHARSET=utf8

唯一约束 UNIQUE

可以为 NULL

CREATE TABLE 表名(
	列名 数据类型 UNIQUE,
	……
)CHARSET=utf8

自动增长列 AUTO_INCREAMENT

必须为主键,数值自动 + 1

CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
	……
)CHARSET=utf8

自动增长列 IDENTITY(M, N)

数值从 M 开始,每次增长 N

CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY IDENTITY(M,N),
	……
)CHARSET=utf8

非空约束 NOT NULL

CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
	……
)CHARSET=utf8

默认值约束 DEFAULT 值

CREATE TABLE 表名(
	列名 数据类型 DEFAULT,
	……
)CHARSET=utf8

外键约束 FOREIGN KEY

引用名:fk_引用表名_引用列名
删除时先删除引用表再删除被引用表

CREATE TABLE 表名(
	列名 数据类型,
	CONSTRAINT 引用名 FOREIGN KEY(列名) REFERENCES 被引用表名(被引用列名) ON DELETE/UPDATE
CASCADE/SET NULL,
	……
)CHARSET=utf8

索引

加快查询速度,但数值必须唯一,并且条件限定时必须使用

索引和主键的区别

主键索引与唯一索引的区别
1. 主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。
2. 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。
3. 唯一性索引列允许空值,而主键列不允许为空值。
4. 主键列在创建时,已经默认为空值 ++    唯一索引了。
5. 一个表最多只能创建一个主键,但可以创建多个唯一索引。
6. 主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。
7. 主键可以被其他表引用为外键,而唯一索引不能。

普通索引

无页数条件

唯一索引

可以出现空值,但是只能出现一次

CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
DROP INDEX indexName ON tableName(column(length))

建表索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

组合索引

类似用户名和密码之间需要写组合索引
最左边的字段必须出现,否则索引失效

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

全文索引

全文索引

索引类型

create index indexName using indexType on table(column(length))

B-Tree

二分树,建议用数字当索引,并最好是自动增长列,维护成本低

HASH

hash函数随机访问,只能用于in或者=,其他无效
InnoDB不支持(自带了)

修改 ALTER

基本语句
ALTER TABLE 表名 操作

添加列

ALTER TABLE 表名 ADD 列名 类型

修改列类型

ALTER TABLE 表名 MODIFY 列名 类型

删除列

ALTER TABLE 表名 DROP 列名

修改列名

ALTER TABLE 表名 CHANGE 原列名 列名 类型

添加外键

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) 
REFERENCES 外表表名(对应的表的主键字段名) 
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

修改表名

ALTER TABLE 表名 RENAME 新表名

删除表

DROP TABLE 表名

数据库的操作

创建数据库

CREATE DATABASE 数据库名

展示数据库

SHOW DATABASES

使用数据库

USE 数据库名

删除数据库

DROP DATABASE 数据库名

事务

事务

权限管理

创建用户

CREATE USER 用户名 IDENTIFIDE BY '密码'

授权

GRANT privileges ON 数据库.TO 用户名/public(所有人)

撤销授权

账户需要重新连接数据库才会生效

REVOKE privileges ON 数据库.FROM 用户名

删除用户

账户需要重新连接数据库才会生效

DROP USER 用户名

视图

视图

关系代数

关系代数

ER 图

ER图

范式

范式